<?php
namespace Admin\Controller;
use Think\Controller;

class EnrollController extends BaseController{
    public function enrolllist(){
        $Enroll = M('Enroll');
        $enrolllist = $Enroll->select();
        $this->assign('enrolllist', $enrolllist);
        $this->display();
    }

    public function search(){ //筛选查看处理,涉及多表、不定项查询,可采取ajax
        //默认输出全部结果
        if(I("post.index")==null){
            $enroll = M('Enroll');
            $userinfo = D('Userinfo');
            $enrolllist = $enroll->select();
            foreach($enrolllist as &$v){
                $info = $userinfo->getInfoById($v['uid']);
                foreach($info as $k=>$vo){
                    $v[$k] = $vo;
                }
            }
            $provincelist=C('PROVINCE');
            $politiclist=C('POLITICS_STATUS');
            $nationlist=C('NATION');
            $this->assign('provincelist',$provincelist);
            $this->assign('politiclist',$politiclist);
            $this->assign('nationlist',$nationlist);
            $this->assign('enrolllist',$enrolllist);
            $this->display();
        }
        //根据条件查询报名情况
        else{
            $posts=I('post.');
            $enroll = M('Enroll');
            //因涉及跨表查询，罗列出两个表字段以便归类
            $type1=array("uid","enrolltime","uptime","ispayed","major","examcenter");
            $type2=array("province","stunumber","address","zipcode","telofparent","tel","sex","birthday","politics","isfresh","nation","school","qq");
            $where=array();
            $field="enroll.id as id,enroll.uid as uid,"; //字符串方式设置域
            foreach($posts as $k=>$v){
                if($k!="index"){
                    if(in_array($k,$type1)){
                        $nk='enroll.'.$k;
                        if(!empty($v))$where[$nk]=$v;//因为采用字符串和数组的连续where查询，保证查询成功，过滤空字段
                        $field=$field.$nk.' as '.$k.',';//遍历post设置域
                    }
                    elseif(in_array($k,$type2)){
                        $nk='userinfo.'.$k;
                        if(!empty($v))$where[$nk]=$v;  //同上
                        $field=$field.$nk.' as '.$k.','; //同上
                    }
                }
            }
            $field=rtrim($field,",");
            $res=$enroll
            ->table('art_enroll enroll,art_userinfo userinfo')//跨表查询指定别名
            ->where('enroll.uid=userinfo.uid')
            ->where($where)
            ->field($field)
            ->order('enroll.uid desc' )
            ->select();
            $provincelist=C('PROVINCE');
            $politiclist=C('POLITICS_STATUS');
            $nationlist=C('NATION');
            $this->assign('provincelist',$provincelist);
            $this->assign('politiclist',$politiclist);
            $this->assign('nationlist',$nationlist);
            $this->assign('where',$where);
            $this->assign('field',$field);
            $this->assign('enrolllist',$res);
            $this->assign('posts',$posts);
            $this->display();

        }
    }

    public function enrollmorelist(){
        $Enroll = M('Enroll');
        $Userinfo = D('Userinfo');
        $enrolllist = $Enroll->select();
        foreach($enrolllist as &$v){
            $info = $Userinfo->getInfoById($v['uid']);
            foreach($info as $k=>$vo){
                $v[$k] = $vo;
            }
        }
        trace($enrolllist);
        $this->assign('enrolllist', $enrolllist);
        $this->display();
    }

    public function exportexcel(){
        import('Vendor.Phpexcel.PHPExcel');
        $ExcelObj = new \PHPExcel();
        $ExcelObj->getProperties()->setCreator("Parallel")
        							 ->setLastModifiedBy("Parallel")
        							 ->setTitle("Enroll Infomations")
        							 ->setSubject("Enroll Infomations")
        							 ->setDescription("西南大学艺术类网上报名系统报名信息")
        							 ->setKeywords("西南大学 艺术 校考 PHPExcel php")
        							 ->setCategory("统计信息");
        $ExcelObj->setActiveSheetIndex(0)->setCellValue('A1', 'ID')
                                        ->setCellValue('B1', '姓名')
                                        ->setCellValue('C1', '报名时间')
                                        ->setCellValue('D1', '是否已经支付')
                                        ->setCellValue('E1', '报名专业')
                                        ->setCellValue('F1', '考点')
                                        ->setCellValue('G1', '考试费用');
        $Enroll = D('Enroll');
        $Major = D('Major');
        $Pay = D('Pay');
        $data = $Enroll->select();
        $i = 2;
        foreach($data as $people){
            $ExcelObj->setActiveSheetIndex(0)->setCellValue('A' . $i, $people['id'])
                                            ->setCellValue('B' . $i, getUsername($people['uid']))
                                            ->setCellValue('C' . $i, formatDate($people['enrolltime']))
                                            ->setCellValue('D' . $i, isPayedClear($people['ispayed']))
                                            ->setCellValue('E' . $i, getMajorNamesClear($people['major']))
                                            ->setCellValue('F' . $i, getExamcenterName($people['examcenter']))
                                            ->setCellValue('G' . $i, $Pay->getValue($people['id']));
            $i++;
        }
        $ExcelObj->setActiveSheetIndex(0);
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter = \PHPExcel_IOFactory::createWriter($ExcelObj, 'Excel2007');
        $objWriter->save('php://output');
        exit();
    }

    public function exportexcelwithmoreinfo(){
        import('Vendor.Phpexcel.PHPExcel');
        $ExcelObj = new \PHPExcel();
        $ExcelObj->getProperties()->setCreator("Parallel")
        							 ->setLastModifiedBy("Parallel")
        							 ->setTitle("Enroll Infomations")
        							 ->setSubject("Enroll Infomations")
        							 ->setDescription("西南大学艺术类网上报名系统报名信息")
        							 ->setKeywords("西南大学 艺术 校考 PHPExcel php")
        							 ->setCategory("统计信息");
        $ExcelObj->setActiveSheetIndex(0)->setCellValue('A1', 'ID')
                                        ->setCellValue('B1', '姓名')
                                        ->setCellValue('C1', '报名时间')
                                        ->setCellValue('D1', '是否已经支付')
                                        ->setCellValue('E1', '报名专业')
                                        ->setCellValue('F1', '考点')
                                        ->setCellValue('G1', '考试费用')
                                        ->setCellValue('H1', '高考省份')
                                        ->setCellValue('I1', '考生号')
                                        ->setCellValue('J1', '通讯地址')
                                        ->setCellValue('K1', '邮政编码')
                                        ->setCellValue('L1', '家长电话')
                                        ->setCellValue('M1', '本人电话')
                                        ->setCellValue('N1', '性别')
                                        ->setCellValue('O1', '生日')
                                        ->setCellValue('P1', '政治面貌')
                                        ->setCellValue('Q1', '应往届')
                                        ->setCellValue('R1', '文理科')
                                        ->setCellValue('S1', '民族')
                                        ->setCellValue('T1', '所在学校')
                                        ->setCellValue('U1', '本人qq')
                                        ->setCellValue('V1', '身份证号');
        $Enroll = D('Enroll');
        $Major = D('Major');
        $Pay = D('Pay');
        $Userinfo = D('Userinfo');
        $data = $Enroll->select();
        $i = 2;
        foreach($data as $people){
            $info = $Userinfo->where(array('uid'=>$people['uid']))->find();
            $ExcelObj->setActiveSheetIndex(0)->setCellValue('A' . $i, $people['id'])
                                            ->setCellValue('B' . $i, getUsername($people['uid']))
                                            ->setCellValue('C' . $i, formatDate($people['enrolltime']))
                                            ->setCellValue('D' . $i, isPayedClear($people['ispayed']))
                                            ->setCellValue('E' . $i, getMajorNamesClear($people['major']))
                                            ->setCellValue('F' . $i, getExamcenterName($people['examcenter']))
                                            ->setCellValue('G' . $i, $Pay->getValue($people['id']))
                                            ->setCellValue('H' . $i, getProvinceNameClear($info['province']))
                                            ->setCellValue('I' . $i, '\'' . $info['stunumber'])
                                            ->setCellValue('J' . $i, $info['address'])
                                            ->setCellValue('K' . $i, '\'' . $info['zipcode'])
                                            ->setCellValue('L' . $i, $info['telofparent'])
                                            ->setCellValue('M' . $i, '\'' . $info['tel'])
                                            ->setCellValue('N' . $i, getSex($info['sex']))
                                            ->setCellValue('O' . $i, $info['birthday'])
                                            ->setCellValue('P' . $i, getPolitics($info['politics']))
                                            ->setCellValue('Q' . $i, getisfresh($info['isfresh']))
                                            ->setCellValue('R' . $i, getScienceFocus($info['is_science_focus']))
                                            ->setCellValue('S' . $i, getNation($info['nation']))
                                            ->setCellValue('T' . $i, $info['school'])
                                            ->setCellValue('U' . $i, '\'' . $info['qq'])
                                            ->setCellValue('V' . $i, '\'' . getAccount($people['uid']));
            $i++;
        }
        $ExcelObj->setActiveSheetIndex(0);
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter = \PHPExcel_IOFactory::createWriter($ExcelObj, 'Excel2007');
        $objWriter->save('php://output');
        exit();
    }

    public function exportexcelwithmoreinfo1(){
        import('Vendor.Phpexcel.PHPExcel');
        $ExcelObj = new \PHPExcel();
        $ExcelObj->getProperties()->setCreator("Parallel")
                                     ->setLastModifiedBy("Parallel")
                                     ->setTitle("Enroll Infomations")
                                     ->setSubject("Enroll Infomations")
                                     ->setDescription("西南大学艺术类网上报名系统报名信息")
                                     ->setKeywords("西南大学 艺术 校考 PHPExcel php")
                                     ->setCategory("统计信息");
        $ExcelObj->setActiveSheetIndex(0)->setCellValue('A1', 'ID')
                                        ->setCellValue('B1', '姓名')
                                        ->setCellValue('C1', '报名时间')
                                        ->setCellValue('D1', '是否已经支付')
                                        ->setCellValue('E1', '报名专业')
                                        ->setCellValue('F1', '考点')
                                        ->setCellValue('G1', '考试费用')
                                        ->setCellValue('H1', '高考省份')
                                        ->setCellValue('I1', '考生号')
                                        ->setCellValue('J1', '通讯地址')
                                        ->setCellValue('K1', '邮政编码')
                                        ->setCellValue('L1', '家长电话')
                                        ->setCellValue('M1', '本人电话')
                                        ->setCellValue('N1', '性别')
                                        ->setCellValue('O1', '生日')
                                        ->setCellValue('P1', '政治面貌')
                                        ->setCellValue('Q1', '应往届')
                                        ->setCellValue('R1', '文理科')
                                        ->setCellValue('S1', '民族')
                                        ->setCellValue('T1', '所在学校')
                                        ->setCellValue('U1', '本人qq')
                                        ->setCellValue('V1', '身份证号');
        $Enroll = D('Enroll');
        $Major = D('Major');
        $Pay = D('Pay');
        $Userinfo = D('Userinfo');
        $data = $Enroll->select();
        $i = 2;
        foreach($data as $people){
            $info = $Userinfo->where(array('uid'=>$people['uid']))->find();
            $ExcelObj->setActiveSheetIndex(0)->setCellValue('A' . $i, $people['id'])
                                            ->setCellValue('B' . $i, getUsername($people['uid']))
                                            ->setCellValue('C' . $i, formatDate($people['enrolltime']))
                                            ->setCellValue('D' . $i, isPayedClear($people['ispayed']))
                                            ->setCellValue('E' . $i, getMajorNamesClear($people['major']))
                                            ->setCellValue('F' . $i, getExamcenterName($people['examcenter']))
                                            ->setCellValue('G' . $i, $Pay->getValue($people['id']))
                                            ->setCellValue('H' . $i, getProvinceNameClear($info['province']))
                                            ->setCellValue('I' . $i, '\'' . $info['stunumber'])
                                            ->setCellValue('J' . $i, $info['address'])
                                            ->setCellValue('K' . $i, '\'' . $info['zipcode'])
                                            ->setCellValue('L' . $i, $info['telofparent'])
                                            ->setCellValue('M' . $i, '\'' . $info['tel'])
                                            ->setCellValue('N' . $i, getSex($info['sex']))
                                            ->setCellValue('O' . $i, $info['birthday'])
                                            ->setCellValue('P' . $i, getPolitics($info['politics']))
                                            ->setCellValue('Q' . $i, getisfresh($info['isfresh']))
                                            ->setCellValue('R' . $i, getScienceFocus($info['is_science_focus']))
                                            ->setCellValue('S' . $i, getNation($info['nation']))
                                            ->setCellValue('T' . $i, $info['school'])
                                            ->setCellValue('U' . $i, '\'' . $info['qq'])
                                            ->setCellValue('V' . $i, '\'' . getAccount($people['uid']));
            $i++;
        }
        $ExcelObj->setActiveSheetIndex(0);
        // Redirect output to a client’s web browser (Excel2007)
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="01simple.xlsx"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');

        // If you're serving to IE over SSL, then the following may be needed
        header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
        header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header ('Pragma: public'); // HTTP/1.0
        $objWriter = \PHPExcel_IOFactory::createWriter($ExcelObj, 'Excel2007');
        $objWriter->save('php://output');
        exit();
    }
}
